package com.google.code.sharecontent.dao.impl;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import org.apache.log4j.Logger;

import com.google.code.sharecontent.action.ResultContainer;
import com.google.code.sharecontent.dao.BaseDao;
import com.google.code.sharecontent.model.Content;
import com.google.code.sharecontent.model.Model;
import com.google.code.sharecontent.model.Paging;


public abstract class BaseDaoImpl<T extends Model> implements BaseDao<T> {
	static Logger logger = Logger.getLogger(BaseDaoImpl.class.getName());
	public static final String DATE_TIME_FORMAT_SQL = "yyyy-MM-dd HH:mm:ss";
	
	protected Connection conn;
	
	public BaseDaoImpl(Connection conn) {
		this.conn = conn;
	}

	public T get(Integer id) throws SQLException {
		String sql = "select * from "+getTableName()+" where ID = "+id;
		Statement statement = this.conn.createStatement();
		logger.debug(sql);
		ResultSet rs = statement.executeQuery(sql);
		if (rs.next()) {
			return createByRow(rs);
		}
		return null;
	}

	public ArrayList<T> list()  throws SQLException {
		return list(null,null);
	}
	
	public ArrayList<T> list(String order)  throws SQLException {
		return list(null,order);		
	}

	public ResultContainer<T> list(Paging paging,String where,String order, Object... values) throws SQLException {
		ResultContainer<T> resultContainer = new ResultContainer<T>();
		ArrayList<T> result = new ArrayList<T>();
		String sql = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		//calculate
		sql = "select count(*) from "+getTableName()+" ";
		statement = generateListQuery(where, null, sql, values);
		rs = statement.executeQuery();
		if (rs.next()) {
			paging.setAllRows(rs.getInt(1));
			if (paging.getAllRows() != null && paging.getAllRows().intValue()>0) {
				//parse rows
				sql = "select * from "+getTableName()+" ";
				statement = generateListQuery(where, order, sql, values);
				rs = statement.executeQuery();
				int startId = 0;
				int stopId = paging.getAllRows();
				paging.setAllPages(1);
				if (paging.getLineOnPage() > 0 ) {
					startId = paging.getPage()*paging.getLineOnPage();
					stopId = (paging.getPage()+1)*paging.getLineOnPage();
					paging.setAllPages(1 + paging.getAllRows() / paging.getLineOnPage());
				}
				paging.generatePages();
				
				int i=0;
				while (rs.next()) {
					if (startId <= i && stopId > i) {
						result.add(createByRow(rs));
					}
					i++;
				}
			}
		}
		resultContainer.setPaging(paging);
		resultContainer.setList(result);
		return resultContainer;
	}
	
	public ArrayList<T> list(String where,String order, Object... values) throws SQLException {
		ArrayList<T> result = new ArrayList<T>();
		String sql = "select * from "+getTableName()+" ";
		PreparedStatement statement = generateListQuery(where, order, sql, values);
		ResultSet rs = statement.executeQuery();
		while (rs.next()) {
			result.add(createByRow(rs));
		}
		return result;
	}

	/**
	 * полностью подготавливается запрос в базу данных
	 * @param where
	 * @param order
	 * @param sql
	 * @param values
	 * @return
	 * @throws SQLException
	 */
	private PreparedStatement generateListQuery(String where, String order, String sql, Object... values) throws SQLException {
		if (where != null && where.length()>0) {
			sql += " where "+where;
		}
		if (order != null && order.length()>0) {
			sql += " order by "+order;
		}
		PreparedStatement statement = this.conn.prepareStatement(sql);
		if (values != null && values.length>0) {
			int i=0;
			for (Object object : values) {
				i++;
				if (object == null) {
					statement.setNull(i, Types.OTHER);
					/**
					 * FIXME Types.OTHER - ацкий эксперимент!
					 */
				}
				else if (object instanceof Integer) {
					statement.setInt(i, (Integer)object);
				}
				else if (object instanceof String) {
					statement.setString(i, (String)object);
				}
			}
		}
		return statement;
	}
	
	public boolean remove(T object) {
		if (object != null) {
			return this.remove(object.getId());
			/*String sql = "delete from "+getTableName()+" where id = "+object.getId();
			try {
				Statement stat = this.conn.createStatement();
				stat.executeUpdate(sql);
			} catch (SQLException e) {
				e.printStackTrace();
			}*/
		}
		return false;
	}
	
	public boolean remove(Integer id) {
		if (id != null) {
			String sql = "delete from "+getTableName()+" where id = "+id;
			try {
				Statement stat = this.conn.createStatement();
				return stat.executeUpdate(sql) >0 ? true : false;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}
	
	public T save(T object) throws SQLException {
		if (object != null) {
			if (object.getId() == null || object.getId()==0) {
				String sql = generateInsertSql(object);
				try {
//					logger.debug(sql);
					PreparedStatement pStat = this.conn.prepareStatement(sql);
					pStat = putPrepareStatement(pStat,object);
					pStat.execute();
					Statement stat = this.conn.createStatement();
//					stat.executeUpdate(sql);
//					stat.getGeneratedKeys();
//					sql = "select  IDENTITY_VAL_LOCAL() from "+getTableName();
					sql = "select LAST_INSERT_ID()";
					logger.debug(sql);
					ResultSet rs = stat.executeQuery(sql);
					if (rs != null && rs.next()) {
						object.setId(rs.getInt(1));
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			} else {
				String sql = generateUpdateSql(object);
				try {
					PreparedStatement pStat = this.conn.prepareStatement(sql);
					pStat = putPrepareStatement(pStat,object);
					pStat.execute();
//					Statement stat = this.conn.createStatement();
//					stat.executeUpdate(sql);
				} catch (SQLException e) {
					throw e;
				}
			}
			if (object != null && object.getId() != null) {
				object = get(object.getId());
			}
		}
		return object;
	}
	
	abstract protected PreparedStatement putPrepareStatement(PreparedStatement pStat, T status) throws SQLException;
	
/*	public T save(T object) throws SQLException {
		if (object != null) {
			if (object.getId() == null || object.getId()==0) {
				String sql = generateInsertSql(object);
				try {
					Statement stat = this.conn.createStatement();
					logger.debug(sql);
					stat.executeUpdate(sql);
					stat.getGeneratedKeys();
					sql = "select  IDENTITY_VAL_LOCAL() from "+getTableName();
					logger.debug(sql);
					ResultSet rs = stat.executeQuery(sql);
					if (rs != null && rs.next()) {
						object.setId(rs.getInt(1));
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			} else {
				String sql = generateUpdateSql(object);
				try {
					Statement stat = this.conn.createStatement();
					stat.executeUpdate(sql);
				} catch (SQLException e) {
					throw e;
				}
			}
			if (object != null && object.getId() != null) {
				object = get(object.getId());
			}
		}
		return object;
	}*/
	
	protected Boolean intToBoolean(int i) {
		return (i == 1 ? Boolean.TRUE : Boolean.FALSE);
	}
	
	protected int booleanToInt(Boolean b) {
		return (b != null && b.booleanValue() ? 1 : 0);
	}

	protected String getStringWithQuotesOrNull(String text) {
		if (text == null) {
			return "null";
		}
		return "'"+text+"'";
	}
	
	protected String getTIMESTAMPWithQuotesOrNull(Date date) {
		if (date == null) {
			return "null";
		}
		Timestamp timestamp = new Timestamp(date.getTime());
		return "'" +timestamp.toString() +"'";
//		return "'" +new SimpleDateFormat("yyyy-mm-dd hh:mm:ss.f...").format(date) +"'";
	}
	
	
	public abstract T createByRow(ResultSet rs) throws SQLException ;
	
	abstract protected String getTableName() ;
	
	protected String generateUpdateSql(T payment) {
		return "";
	}

	protected String generateInsertSql(T payment) {
		return "";
	}
	
}
